﻿-- =============================================
-- Author:		GSJ
-- Create date: 2015-1-7
-- Description:	生成每日银行发生额--手动工具
-- =============================================
CREATE PROCEDURE [dbo].[proc_Statistics_Financial_YHXJ_ByCompanyId_Tool]
(
	@Companyid int, 
	@idate datetime
)
AS
BEGIN

Declare @sRq Datetime,@eRq Datetime,@sRq_o Datetime,@eRq_o Datetime
Set @sRq = Convert(Datetime, Convert(varchar(10),@idate,120) + ' 00:00:00')
Set @eRq = Convert(Datetime, Convert(varchar(10),@idate,120) + ' 23:59:59')

Set @sRq_o = Convert(Datetime, Convert(varchar(10),DATEADD(dd,-1,@idate),120) + ' 00:00:00')
Set @eRq_o = Convert(Datetime, Convert(varchar(10),DATEADD(dd,-1,@idate),120) + ' 23:59:59')

--删除本公司当日的现金银行明细
delete BankYE where companyid=@Companyid and [date] between @sRq and @eRq

--重新计算当日的现金银行明细
;WITH xjsr as(
--现金收入
 SELECT SUM([Income]) as je,BankId,companyid FROM [Finance_RunningAccount] WHERE Companyid=@Companyid and
  createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 ))
 group by  BankId,CompanyId
 ),
 xjsr_non as(
  SELECT SUM([Income]) as je,BankId,companyid FROM Finance_RunningAccount_Non WHERE Companyid=@Companyid and
   createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 ))
 group by  BankId,CompanyId
 ),
 xzzc as (
 --现金支出
 SELECT sum(outlayprice) as je,BankId,companyidcur as companyid
  FROM [Finance_Outlay] 
WHERE Companyidcur=@Companyid and  [cb_flag]=0 and createdate>=@sRq and createdate<=@eRq   AND (PaymentTypeId=1 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 )) 
  group by BankId,companyidcur
 ),

cx as(
--存现
 SELECT sum(cx) as je,cx_BankId as BankId,companyid  FROM txcx t WHERE Companyid=@Companyid and
  createdate>=@sRq and createdate<=@eRq  AND  (t.cx_PaymentTypeId=1 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 ))
 group by cx_BankId,CompanyId
 ),
 tx as(
 --提现
 SELECT sum(tx) as je ,tx_BankId as BankId,companyid FROM txcx t WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq  AND  (t.tx_PaymentTypeId=1 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=1 ))
 group by tx_BankId,CompanyId
 ),
 bank as (
 
 select fb.companyid,fb.id,fb.openbank,
 isnull((select je from xjsr where xjsr.bankid=fb.id),0) +
 isnull((select je from xjsr_non where xjsr_non.bankid=fb.id),0) -
  isnull((select je from xzzc where xzzc.bankid=fb.id),0) +

  isnull((select je from cx where cx.bankid=fb.id),0)     -
  isnull((select je from tx where tx.bankid=fb.id),0) as hjje 
  
 from Finance_Bank as fb where  PaymentTypeId =1 and companyid=@Companyid
 union all
 select c.id,1,'现金',
 isnull((select je from xjsr where xjsr.bankid=1 and xjsr.companyid=c.id),0) +
 isnull((select je from xjsr_non where xjsr_non.bankid=1 and xjsr_non.companyid=c.id),0) -
  isnull((select je from xzzc where xzzc.bankid=1 and xzzc.companyid=c.id),0) +
 
  isnull((select je from cx where cx.bankid=1 and cx.companyid=c.id),0)     -
  isnull((select je from tx where tx.bankid=1 and tx.companyid=c.id),0) as hjje
 from company as c where id=@Companyid
 )
 
 insert into BankYE([date],companyid,bankid,bankname,drje,flag,ye)
 select @idate,companyid,id,openbank,hjje,1,isnull((select ye from bankye where bankye.companyid=bank.companyid and
 bankye.bankid=bank.id and
  [date] between @sRq_o and @eRq_o),0)+hjje from bank 
 order by id
 --------------------------------------------------------------------------
 
;WITH yhsr as(
--银行收入
 SELECT SUM([Income]) as je,BankId,CompanyId FROM [Finance_RunningAccount] WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 ))
 group by  BankId,CompanyId
 ),
 yhsr_non as (
  SELECT SUM([Income]) as je,BankId,CompanyId FROM Finance_RunningAccount_Non WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq 
 AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 ))
 group by  BankId,CompanyId
 ),
 yhzc as (
 --银行支出
 SELECT sum(outlayprice) as je,BankId,companyidcur as companyid
  FROM [Finance_Outlay] 
WHERE  Companyidcur=@Companyid and [cb_flag]=0 and createdate>=@sRq and createdate<=@eRq   AND (PaymentTypeId=3 or BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 )) 
  group by BankId,companyidcur
 ),

cx_yh as(
--存现
 SELECT sum(cx) as je,cx_BankId as BankId,CompanyId  FROM txcx t WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq  AND  (t.cx_PaymentTypeId=3 or t.cx_BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 ))
 group by cx_BankId,CompanyId
 ),
 tx_yh as(
 --提现
 SELECT sum(tx) as je ,tx_BankId as BankId,CompanyId FROM txcx t WHERE Companyid=@Companyid and createdate>=@sRq and createdate<=@eRq  AND  (t.tx_PaymentTypeId=3 or t.tx_BankId IN (SELECT id FROM Finance_Bank fb WHERE Companyid=@Companyid and fb.PaymentTypeId=2 ))
 group by tx_BankId,CompanyId
 ),
bank1 as (
 
 select CompanyId,fb.id,fb.openbank,
 isnull((select je from yhsr where yhsr.bankid=fb.id),0) +
 isnull((select je from yhsr_non where yhsr_non.bankid=fb.id),0) -
 isnull((select je from yhzc where yhzc.bankid=fb.id),0) +
 
  isnull((select je from cx_yh where cx_yh.bankid=fb.id),0)     -
  isnull((select je from tx_yh where tx_yh.bankid=fb.id),0) as hjje
  
  
 from Finance_Bank as fb where  PaymentTypeId =2 and companyid=@Companyid
 union all
 select c.id,3,'支票',
  isnull((select je from yhsr where yhsr.bankid=3 and yhsr.companyid=c.id),0) +
  isnull((select je from yhsr_non where yhsr_non.bankid=3 and yhsr_non.companyid=c.id),0) -
  isnull((select je from yhzc where yhzc.bankid=3 and yhzc.companyid=c.id),0) +

  isnull((select je from cx_yh where cx_yh.bankid=3 and cx_yh.companyid=c.id),0)     -
  isnull((select je from tx_yh where tx_yh.bankid=3 and tx_yh.companyid=c.id),0) as hjje
 from company as c where id=@Companyid
)

 insert into BankYE([date],companyid,bankid,bankname,drje,flag,ye)
 select @idate,companyid,id,openbank,hjje,2,isnull((select ye from bankye where bankye.companyid=bank1.companyid and
 bankye.bankid=bank1.id and
  [date] between @sRq_o and @eRq_o),0)+hjje from bank1 
 order by id


END
